namespace ETestIT.DataAccess
{
    using System;
    using Domain.Entities;
    using Domain.Repositories;
    using Repository;

    public class CandidateResumeRepositoryImpl : ICandidateResumeRepository
    {

        public CandidateResume GetById(Guid candidateResumeId)
        {
            const string query = @"SELECT 
	                                    CR.Id,
	                                    C.Id as IdCandidate,
	                                    FirstName,
	                                    Surename,
	                                    Title,
	                                    R.Id as IdResume,
	                                    Name,
	                                    Content,
	                                    ContentType,
                                        CR.IdJobTitle
                                    FROM CandidateResumes CR
	                                    LEFT JOIN Candidats C ON C.Id = CR.IdCandidate
	                                    LEFT JOIN Resumes R ON R.Id = CR.IdResume
                                    WHERE CR.Id = @Id";

            return new FluentCommand<CandidateResume>(query)
                .AddGuid("Id", candidateResumeId)
                .SetMap(CandidateResumeMap)
                .AsSingle();
        }

        public CandidateResume GetByResumeId(Guid resumeId)
        {
            const string query = @"SELECT 
	                                    CR.Id,
	                                    C.Id as IdCandidate,
	                                    FirstName,
	                                    Surename,
	                                    Title,
	                                    R.Id as IdResume,
	                                    Name,
	                                    Content,
	                                    ContentType,
                                        CR.IdJobTitle
                                    FROM CandidateResumes CR
	                                    LEFT JOIN Candidats C ON C.Id = CR.IdCandidate
	                                    LEFT JOIN Resumes R ON R.Id = CR.IdResume
                                    WHERE R.Id = @Id";

            return new FluentCommand<CandidateResume>(query)
                .AddGuid("Id", resumeId)
                .SetMap(CandidateResumeMap)
                .AsSingle();
        }

        private CandidateResume CandidateResumeMap(DataReader reader)
        {
            return new CandidateResume
                       {
                           Id = reader.GetGuid("Id"),
                           TheCandidate = new Candidate()
                                              {
                                                  Id = reader.GetGuid("IdCandidate"),
                                                  FirstName = reader.GetString("FirstName"),
                                                  Surename = reader.GetString("Surename"),
                                                  Title = reader.GetString("Title")
                                              },
                           TheResume = new Resume()
                                           {
                                               Id = reader.GetGuid("IdResume"),
                                               Content = reader.GetValue<byte[]>("Content"),
                                               Name = reader.GetString("Name"),
                                               ContentType = reader.GetString("ContentType")
                                           },
                           IdJobTitle = reader.GetGuid("IdJobTitle")
                       };
        }

        public void Add(CandidateResume candidateResume)
        {
            candidateResume.Id = Guid.NewGuid();

            new FluentCommand<string>(@"INSERT INTO CandidateResumes (Id, IdCandidate, IdResume, IdJobTitle)
                                        VALUES (@Id, @IdCandidate, @IdResume, @IdJobTitle)")
                .AddGuid("Id", candidateResume.Id)
                .AddGuid("IdCandidate", candidateResume.TheCandidate.Id)
                .AddGuid("IdResume", candidateResume.TheResume.Id)
                .AddGuid("IdJobTitle", candidateResume.IdJobTitle)
                .AsNonQuery();
        }

        public void Delete(Guid candidateResumeId)
        {
            new FluentCommand<string>(@"DELETE FROM CandidateResumes WHERE Id = @Id")
                .AddGuid("Id", candidateResumeId)
                .AsNonQuery();
        }
    }
}